﻿using BlazorAdmin.Generator.Models;
using System;
using System.Collections.Generic;
using System.Data.Common;

namespace BlazorAdmin.Generator.DbColumnReaders
{
    public class SqlServerColumnReader : DbColumnReader
    {
        public override List<Column> ReadColumns(DbConnection connection, DbProviderFactory factory, string table)
        {
            var cols = new List<Column>();

            var strSql = @$"SELECT 
                                Cols.COLUMN_NAME AS Code, 
                                COALESCE(
                                    (SELECT ep.value 
                                     FROM sys.extended_properties AS ep 
                                     JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id 
                                     WHERE c.object_id = Clas.object_id AND c.name = Cols.COLUMN_NAME), 
                                    '') AS Name,
                                Cols.DATA_TYPE AS DbType,
                                COALESCE(Cols.CHARACTER_MAXIMUM_LENGTH, Cols.NUMERIC_PRECISION, Cols.DATETIME_PRECISION, 0) AS Length, 
                                COALESCE(Cols.NUMERIC_SCALE, 0) AS Point, 
                                CASE WHEN Cols.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END AS IsNull,
                                CASE WHEN EXISTS (
                                    SELECT 1 
                                    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC 
                                    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU 
                                    ON TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME 
                                    WHERE TC.TABLE_NAME = Cols.TABLE_NAME 
                                    AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY' 
                                    AND KCU.COLUMN_NAME = Cols.COLUMN_NAME
                                ) THEN 1 ELSE 0 END AS IsKey
                            FROM INFORMATION_SCHEMA.COLUMNS AS Cols
                            JOIN sys.objects AS Clas ON Clas.name = Cols.TABLE_NAME AND Clas.type = 'U'
                            JOIN sys.columns AS Attr ON Attr.object_id = Clas.object_id AND Attr.name = Cols.COLUMN_NAME
                            WHERE Cols.TABLE_NAME = '{table}';";

            using (var cmd = factory.CreateCommand())
            {
                cmd.Connection = connection;
                cmd.CommandText = strSql;
                var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    cols.Add(new Column()
                    {
                        PropertyCode = reader["Code"].ToString(),
                        PropertyDesc = reader["Name"].ToString(),
                        PropertyType = GetPropertyType(reader["DbType"].ToString()),
                        Length = Convert.ToInt32(reader["Length"]),
                        Point = Convert.ToInt32(reader["Point"]),
                        IsNullable = Convert.ToBoolean(reader["IsNull"]),
                        IsKey = Convert.ToBoolean(reader["IsKey"]),
                    });
                }
            }

            return SetInputTypeAuto(cols);
        }

        string GetPropertyType(string dbType)
        {
            switch (dbType.Trim())
            {
                case "int":
                    return "int";
                case "bigint":
                    return "long";
                case "smallint":
                    return "short";
                case "tinyint":
                    return "byte";
                case "bit":
                    return "bool";
                case "decimal":
                case "numeric":
                case "money":
                case "smallmoney":
                    return "decimal";
                case "float":
                    return "double";
                case "real":
                    return "float";
                case "datetime":
                case "datetime2":
                case "smalldatetime":
                case "date":
                    return "DateTime";
                case "time":
                    return "TimeSpan";
                case "varbinary":
                case "binary":
                case "image":
                    return "byte[]";
                default:
                    return "string";
            }
        }
    }
}
